package com.libraries.helper;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

/**
 * 数据库升级辅助类
 * @author Wellsa
 *
 */
public class SqliteHelper {
	private static final String TAG="UpdateHelper";
	private static final String TEMP_SUFFIX = "_temp"; // 数据库更新时，表名后缀
    
    /**
     * 升级数据库表
     * @param db
     * @param tableName
     * @param newColumns
     * @param createSql
     */
    public static void doUpgrade(SQLiteDatabase db,String tableName,String[] newColumns,String createSql){
    	Logger.log(TAG, "doUpgrade", tableName,createSql);
    	try {
    		db.beginTransaction();
        	String coulmns=getOldColumns(db, tableName,newColumns);
        	if (coulmns==null||coulmns.equals("")) {
    			return;
    		}
        	renameTable(db,tableName);
        	createNewTable(db, createSql);
        	backupTable(db, tableName,coulmns);
        	dropTempTable(db, tableName);
        	db.setTransactionSuccessful();
//        	Logger.log(TAG, "updateTable ~", getOldColumns(db, tableName));
		} catch (Exception e) {
			Logger.log(TAG, "doUpgrade err", e);
		}
    	finally {
            db.endTransaction();
        }
    }
    
    /**
     * 获取所有字段名
     * @param db
     * @param tableName
     * @return
     */
    private static String getOldColumns(SQLiteDatabase db, String tableName,String[] newColumns)
    {
        StringBuffer sbCoulmns = new StringBuffer();
        Cursor c = null;
        try {
            c = db.rawQuery("PRAGMA table_info(" + tableName + ")", null);
            if (null != c) {
            	int columnIndex = c.getColumnIndex("name");
            	if (-1 == columnIndex) {
            		return null;
            	}
            	int index = 0;
            	while (c.moveToNext()) {
            		String column=c.getString(columnIndex);
            		if (newColumns!=null&&
            				!needColumn(column, newColumns)) {
						continue;
					}
                    if (index+1== c.getCount()) {
                    	sbCoulmns.append(column);
                    }
                    else{
                    	sbCoulmns.append(column+",");
                    }
                    index++;
				}
            	Logger.log(TAG,"getOldColumns",c.getCount(),sbCoulmns.toString());
            }
        } catch (Exception e) {
        	e.printStackTrace();
            Logger.log(TAG,"getOldColumns err",e.getMessage());
        } finally {
            if (null != c) {
                c.close();
            }
        }
        return sbCoulmns.toString();
    }
    
    /**
     * 判断旧的字段是否需要（在新字段里）
     * @param oldColumn
     * @param newColumns
     * @return
     */
    private static boolean needColumn(String oldColumn,String[] newColumns){
    	for (String newColumn : newColumns) {
			if (oldColumn.equals(newColumn)) {
				return true;
			}
		}
    	return false;
    }
    
    private static void renameTable(SQLiteDatabase db,String tableName) {
    	Logger.log(TAG, "renameTable", db,tableName);
    	db.execSQL("alter table " + tableName + " rename to " + tableName + TEMP_SUFFIX);
    }
    
    private static void createNewTable(SQLiteDatabase db,String createSql) {
    	Logger.log(TAG, "createNewTable", db,createSql);
    	db.execSQL(createSql);
    }
    
    private static void backupTable(SQLiteDatabase db,String tableName,String coulmns){
    	Logger.log(TAG, "backupTable", db,tableName,coulmns);
    	String backupSql = "insert into " + tableName + " ("+ coulmns + ") "
    			+ "select " + coulmns + " from " + tableName+ TEMP_SUFFIX;
    	db.execSQL(backupSql);
    }

    private static void dropTempTable(SQLiteDatabase db,String tableName) {
    	Logger.log(TAG, "dropTempTable", db,tableName);
    	 db.execSQL("DROP TABLE IF EXISTS "+tableName+ TEMP_SUFFIX);
    }

}
